CREATE TABLE [COUNTRY](
	[id] [int] IDENTITY(1, 1),
	[country] [varchar](30) NOT NULL,
	CONSTRAINT [PK_COUNTRY] PRIMARY KEY  ([id] ASC)
);

CREATE TABLE [MAIN_FEATURE](
	[id] [int] IDENTITY(1, 1),
	[main_feature] [varchar](60) NOT NULL,
	CONSTRAINT [PK_MAIN_FEATURE] PRIMARY KEY  ([id] ASC)
);

CREATE TABLE [PLACE_OF_REST](
	[id] [int] IDENTITY(1, 1),
	[main_feature_id] [int],
	[place] [varchar](20) NOT NULL,
	[description] [varchar](500) NOT NULL,
	CONSTRAINT [PK_PLACE_OF_REST] PRIMARY KEY  ([id] ASC)
);

ALTER TABLE [PLACE_OF_REST]  
	ADD CONSTRAINT [FK_PLACE_FEATURE] FOREIGN KEY([main_feature_id])
		REFERENCES [MAIN_FEATURE] ([id])

CREATE TABLE [COUNTRY_PLACE](
	[country_id] [int] NOT NULL,
	[place_id] [int] NOT NULL,
	CONSTRAINT [PK_COUNTRY_PLACE] PRIMARY KEY  ([country_id] ASC, [place_id] ASC)
);

ALTER TABLE [COUNTRY_PLACE] 
	ADD CONSTRAINT [FK_COUNTRY_PLACE_COUNTRY] FOREIGN KEY([country_id])
		REFERENCES [COUNTRY] ([id])

ALTER TABLE [COUNTRY_PLACE] 
	ADD CONSTRAINT [FK_COUNTRY_PLACE_PLACE] FOREIGN KEY([place_id])
		REFERENCES [PLACE_OF_REST] ([id])

CREATE TABLE [TOUR](
	[id] [int] IDENTITY(1, 1),
	[name] [varchar](30) NOT NULL,
	CONSTRAINT [PK_TOUR] PRIMARY KEY  ([id] ASC)
);

CREATE TABLE [GROUP](
	[id] [int] IDENTITY(1, 1),
	[tour_id] [int],
	[global_start] [datetime] NOT NULL,
	[global_end] [datetime] NOT NULL,
	[discount] [int] NULL,
	[price] [int] NOT NULL,
	CONSTRAINT [PK_GROUP] PRIMARY KEY ([id] ASC)
);

ALTER TABLE [GROUP] ALTER COLUMN 
	[price] [numeric](9, 2) NOT NULL

ALTER TABLE [GROUP]  
	ADD CONSTRAINT [FK_GROUP_TOUR] FOREIGN KEY([tour_id])
		REFERENCES [TOUR] ([id])

CREATE TABLE [HOTEL](
	[id] [int] IDENTITY(1, 1),
	[hotel] [varchar](50) NOT NULL,
	[stars] [int] NOT NULL,
	CONSTRAINT [PK_HOTEL] PRIMARY KEY  ([id] ASC)
);

CREATE TABLE [HOTEL_PLACE](
	[hotel_id] [int] NOT NULL,
	[place_id] [int] NOT NULL,
	CONSTRAINT [PK_HOTEL_PLACE] PRIMARY KEY  ([hotel_id] ASC, [place_id] ASC)
);

ALTER TABLE [HOTEL_PLACE]  
	ADD CONSTRAINT [FK_HOTEL_PLACE_HOTEL] FOREIGN KEY([hotel_id])
		REFERENCES [HOTEL] ([id])

ALTER TABLE [HOTEL_PLACE]  
	ADD CONSTRAINT [FK_HOTEL_PLACE_PLACE_OF_REST] FOREIGN KEY([place_id])
		REFERENCES [PLACE_OF_REST] ([id])

CREATE TABLE [PERSON](
	[id] [int] IDENTITY(1, 1),
	[first_name] [varchar](20) NOT NULL,
	[last_name] [varchar](20) NOT NULL,
	[middle_name] [varchar](15) NULL,
	[phone_number] [varchar](15) NULL,
	[second_number] [varchar](15) NULL,
	[email] [varchar](40) NULL,
	[country_id] [int] NOT NULL,
	CONSTRAINT [PK_PERSON] PRIMARY KEY  ([id] ASC)
);

ALTER TABLE [PERSON] 
	ADD CONSTRAINT [FK_PERSON_COUNTRY] FOREIGN KEY([country_id])
		REFERENCES [COUNTRY] ([id])

CREATE TABLE [PASSPORT](
	[person_id] [int] NOT NULL,
	[country_id] [int] NOT NULL,
	[serial] [varchar](10) NOT NULL,
	[number] [nchar](10) NOT NULL,
	[international] [bit] NOT NULL,
	CONSTRAINT [PK_PASSPORT_1] PRIMARY KEY  ([person_id] ASC, [country_id] ASC, [international] ASC)
);

ALTER TABLE [PASSPORT]  
	ADD CONSTRAINT [FK_PASSPORT_COUNTRY] FOREIGN KEY([country_id])
		REFERENCES [COUNTRY] ([id])

ALTER TABLE [PASSPORT]  
	ADD CONSTRAINT [FK_PASSPORT_PERSON] FOREIGN KEY([person_id])
		REFERENCES [PERSON] ([id])

CREATE TABLE [PERSON_GROUP](
	[group_id] [int] NOT NULL,
	[person_id] [int] NOT NULL,
	CONSTRAINT [PK_PERSON_TOUR_1] PRIMARY KEY  ([group_id] ASC,[person_id] ASC)
);

ALTER TABLE [PERSON_GROUP] 
	ADD CONSTRAINT [FK_PERSON_TOUR_GROUP] FOREIGN KEY([group_id])
		REFERENCES [GROUP] ([id])

ALTER TABLE [PERSON_GROUP]  
	ADD CONSTRAINT [FK_PERSON_TOUR_PERSON] FOREIGN KEY([person_id])
		REFERENCES [PERSON] ([id])

CREATE TABLE [SEA](
	[id] [int] IDENTITY(1, 1),
	[sea] [varchar](50) NOT NULL,
	CONSTRAINT [PK_SEA] PRIMARY KEY  ([id] ASC)
);

CREATE TABLE [PLACE_TOUR](
	[id] [int] IDENTITY(1, 1),
	[place_id] [int],
	[hotel_id] [int],
	[country_id] [int],
	[sea_id] [int],
	CONSTRAINT [PK_PLACE_TOUR] PRIMARY KEY  ([id] ASC)
);

ALTER TABLE [PLACE_TOUR] 
	ADD CONSTRAINT [FK_PLACE_TOUR_COUNTRY1] FOREIGN KEY([country_id])
		REFERENCES [COUNTRY] ([id])

ALTER TABLE [PLACE_TOUR]  
	ADD CONSTRAINT [FK_PLACE_TOUR_HOTEL1] FOREIGN KEY([hotel_id])
		REFERENCES [HOTEL] ([id])

ALTER TABLE [PLACE_TOUR]  
	ADD CONSTRAINT [FK_PLACE_TOUR_PLACE_OF_REST1] FOREIGN KEY([place_id])
		REFERENCES [PLACE_OF_REST] ([id])

ALTER TABLE [PLACE_TOUR]  
	ADD CONSTRAINT [FK_PLACE_TOUR_SEA1] FOREIGN KEY([sea_id])
		REFERENCES [SEA] ([id])

CREATE TABLE [PLACE_TIME](
	[group_id] [int] NOT NULL,
	[place_id] [int] NOT NULL,
	[start_date] [datetime] NOT NULL,
	[end_date] [datetime] NOT NULL,
	CONSTRAINT [PK_PLACE_TIME_1] PRIMARY KEY  ([group_id] ASC,	[place_id] ASC, [start_date] ASC)
);

ALTER TABLE [PLACE_TIME]  
	ADD CONSTRAINT [FK_PLACE_TIME_GROUP] FOREIGN KEY([group_id])
		REFERENCES [GROUP] ([id])

ALTER TABLE [PLACE_TIME]  
	ADD CONSTRAINT [FK_PLACE_TIME_PLACE_TOUR1] FOREIGN KEY([place_id])
		REFERENCES [PLACE_TOUR] ([id])

CREATE TABLE [SEA_PLACE](
	[sea_id] [int] NOT NULL,
	[place_id] [int] NOT NULL,
	CONSTRAINT [PK_SEA_PLACE] PRIMARY KEY  ([sea_id] ASC, [place_id] ASC)
);

ALTER TABLE [SEA_PLACE]  
	ADD CONSTRAINT [FK_SEA_PLACE_PLACE_OF_REST] FOREIGN KEY([place_id])
		REFERENCES [PLACE_OF_REST] ([id])

ALTER TABLE [SEA_PLACE]  
	ADD CONSTRAINT [FK_SEA_PLACE_SEA] FOREIGN KEY([sea_id])
		REFERENCES [SEA] ([id])

CREATE TABLE [TOUR_ROUTE](
	[tour_id] [int] NOT NULL,
	[place_id] [int] NOT NULL,
	CONSTRAINT [PK_TOUR_ROUTE_1] PRIMARY KEY  ([tour_id] ASC, [place_id] ASC)
);

ALTER TABLE [TOUR_ROUTE]  
	ADD CONSTRAINT [FK_TOUR_ROUTE_PLACE_TOUR1] FOREIGN KEY([place_id])
		REFERENCES [PLACE_TOUR] ([id])

ALTER TABLE [TOUR_ROUTE]  
	ADD CONSTRAINT [FK_TOUR_ROUTE_TOUR] FOREIGN KEY([tour_id])
		REFERENCES [TOUR] ([id])

ALTER TABLE [HOTEL]  
	ADD CONSTRAINT [CK_Hotel_Stars] 
		CHECK (([stars] > 0) AND ([stars] < 6));

ALTER TABLE [GROUP]  
	ADD CONSTRAINT [CK_Discount] 
		CHECK (([discount] >= 0) AND ([discount] <= 100 ));

ALTER TABLE [GROUP]  
	ADD CONSTRAINT [CK_Price] 
		CHECK ([price] >= 0);

set DATEFORMAT dmy

ALTER TABLE [GROUP]  
	ADD CONSTRAINT [CK_GDate] 
		CHECK (([global_start] < [global_end]) AND ([global_start] > '01/01/2000') AND ([global_end] < getdate()));

ALTER TABLE [PLACE_TIME]  
	ADD CONSTRAINT [CK_SDate] 
		CHECK (([start_date] < [end_date]) AND ([start_date] > '01/01/2000'));

CREATE UNIQUE INDEX [IX_Country] on [COUNTRY]
(
	[country] ASC
);

CREATE UNIQUE INDEX [IX_Main] on [MAIN_FEATURE]
(
	[main_feature] ASC
);

CREATE UNIQUE INDEX [IX_Sea] on [SEA]
(
	[sea] ASC
);

CREATE UNIQUE INDEX [IX_Email] on [PERSON]
(
	[email] ASC
);